import logging
import random
import time
from contextlib import asynccontextmanager
from datetime import datetime, timedelta

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from app.core.config import settings

from sqlalchemy import event

from app.core.security import get_password_hash
from app.utils.device_info_manager import device_info_manager
import datetime
import pytz
logger = logging.getLogger(__name__)

# 确保使用同步驱动
DATABASE_URL = settings.DATABASE_URL
if DATABASE_URL.startswith("sqlite+aiosqlite"):
    # 自动修正为同步驱动
    DATABASE_URL = DATABASE_URL.replace("sqlite+aiosqlite", "sqlite")
    logger.warning(f"自动修正数据库URL为同步驱动: {DATABASE_URL}")

logger.info(f"使用数据库URL: {DATABASE_URL}")

# 创建同步引擎
engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False} if "sqlite" in DATABASE_URL else {},
    pool_size=settings.POOL_SIZE,
    max_overflow=settings.MAX_OVERFLOW,
    pool_timeout=settings.POOL_TIMEOUT,
    pool_recycle=settings.POOL_RECYCLE,
    pool_pre_ping = True  # 在每次使用前检查连接是否有效
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

@event.listens_for(engine, "connect")
def connect_listener(dbapi_connection, connection_record):
    """监听连接创建事件"""
    logger.debug(f"新连接创建: {id(dbapi_connection)}")

@event.listens_for(engine, "checkout")
def checkout_listener(dbapi_connection, connection_record, connection_proxy):
    """监听连接检出事件"""
    logger.debug(f"连接检出: {id(dbapi_connection)}")

@event.listens_for(engine, "checkin")
def checkin_listener(dbapi_connection, connection_record):
    """监听连接归还事件"""
    logger.debug(f"连接归还: {id(dbapi_connection)}")

# 创建数据库表
Base.metadata.create_all(bind=engine)

@asynccontextmanager
async def get_db_context():
    """异步上下文管理器用于数据库会话"""
    db = SessionLocal()
    try:
        yield db
    finally:
        try:
            db.close()
        except Exception as e:
            logger.error(f"关闭数据库会话失败: {e}")
        finally:
            # 移除多余的 dispose() 调用
            # 只需关闭会话即可，连接会自动归还连接池
            pass

def initialize_database_tables():
    # 显式创建所有表
    Base.metadata.create_all(bind=engine)

    # 打印已创建的表
    from sqlalchemy import inspect
    inspector = inspect(engine)
    table_names = inspector.get_table_names()
    logger.info(f"已创建的表: {table_names}")

    # 确保 devices 表存在
    if 'devices' not in table_names:
        logger.error("devices 表未创建！")
        # 尝试单独创建 devices 表
        Device.__table__.create(bind=engine)
        logger.info("已创建 devices 表")

    # 确保 users 表存在
    if 'users' not in table_names:
        logger.error("users 表未创建！")
        # 尝试单独创建 users 表
        User.__table__.create(bind=engine)
        logger.info("已创建 users 表")

    # 确保 device_registration_logs 表存在
    if 'device_registration_logs' not in table_names:
        logger.error("device_registration_logs 表未创建！")
        # 尝试单独创建表
        DeviceRegistrationLog.__table__.create(bind=engine)
        logger.info("已创建 device_registration_logs 表")


def initialize_device_data():
    db = SessionLocal()
    try:
        # 检查是否已有数据
        device_count = db.query(Device).count()
        logger.info(f"当前设备数量: {device_count}")

        if device_count > 0:
            logger.info("数据库已有设备数据，跳过初始化")
            return

        if settings.TEST_SQLITE_DATABASE:
            logger.info("未启用数据库初始")
        else:
            logger.info("开始初始化设备数据")

            online_statuses = ["online", "offline"]
            versions = ["v1.0.0", "v1.1.0", "v1.2.0", "v2.0.0", "v2.1.0"]

            # 使用集合跟踪已生成的设备ID
            generated_dev_ids = set()

            for i in range(100):
                while True:
                    dev_id = f"DEV-{random.randint(1000, 9999)}"
                    # 确保设备ID唯一（在本次初始化中）
                    if dev_id not in generated_dev_ids:
                        # 检查数据库中是否已存在（虽然数据库为空，但为了健壮性）
                        if not db.query(Device).filter(Device.dev_id == dev_id).first():
                            generated_dev_ids.add(dev_id)
                            break

                device = Device(
                    dev_id=dev_id,
                    dev_name=f"设备{i + 1}",  # 添加设备名
                    cpu_ser=f"CPU-{random.randint(10000000, 99999999)}",
                    emmc_ser=f"EMMC-{random.randint(10000000, 99999999)}",
                    mac_add=":".join(f"{random.randint(0, 255):02x}" for _ in range(6)),
                    activated_time=(datetime.now() - timedelta(days=random.randint(0, 30))).strftime(
                        "%Y-%m-%d %H:%M:%S"),
                    cpu_usage=random.randint(0, 100),
                    mem_usage=random.randint(0, 100),
                    # emmc_usage=random.randint(0, 100),
                    # ip_address=f"192.168.{random.randint(0, 255)}.{random.randint(0, 255)}",
                    firmware_version=random.choice(versions),
                    app_version=random.choice(versions),
                    online_status=random.choice(online_statuses),
                    brightness=random.randint(0, 100) if random.random() > 0.3 else None,
                    volume=random.randint(0, 100) if random.random() > 0.3 else None,
                    last_active=time.time() - random.randint(0, 86400)  # 1天内随机时间
                )
                db.add(device)

            db.commit()
            logger.info(f"成功初始化 {db.query(Device).count()} 条设备数据")
    except Exception as e:
        db.rollback()
        logger.error(f"初始化设备数据失败: {str(e)}")
        raise
    finally:
        db.close()

def initialize_admin_user():
    """初始化管理员用户"""
    db = SessionLocal()
    try:
        # 检查是否已有管理员用户
        admin_user = db.query(User).filter(User.username == "admin").first()
        if admin_user:
            logger.info("管理员用户已存在")
            return

        # 创建管理员用户
        logger.info("创建默认管理员用户")
        hashed_password = get_password_hash("admin123")  # 确保导入 get_password_hash
        admin_user = User(
            username="admin",
            hashed_password=hashed_password,
            nickname="管理员",
            avatar="https://example.com/admin.jpg",
            roles="admin",
            permissions="*:*:*",
            is_active=True
        )
        db.add(admin_user)
        db.commit()
        logger.info("管理员用户创建成功")
    except Exception as e:
        db.rollback()
        logger.error(f"创建管理员用户失败: {str(e)}")
    finally:
        db.close()

# app/database.py
from app.models.models import Device, User, DeviceRegistrationLog
from app.utils.device_cache import device_cache


# app/database.py
async def get_all_devices():
    """获取所有设备 - 完全基于缓存"""
    try:
        # 尝试从Redis获取
        cached_devices = await device_cache.get_all_devices()
        if cached_devices:
            logger.debug("从缓存获取所有设备成功")
            return cached_devices

        # 缓存未命中，从数据库获取
        db = SessionLocal()
        try:
            devices = db.query(Device).all()
            device_dicts = [device.to_dict() for device in devices]

            # 确保每个设备字典包含 'dev_id'
            for device_dict in device_dicts:
                if "dev_id" not in device_dict:
                    device_dict["dev_id"] = device_dict.get("dev_id", "")

            # 更新缓存
            await device_cache.batch_update(device_dicts)
            logger.info(f"从数据库获取 {len(device_dicts)} 台设备并更新缓存")
            return device_dicts
        finally:
            db.close()
    except Exception as e:
        logger.error(f"获取设备列表失败: {e}")
        return []

# app/database.py
async def get_all_devices_db():
    """获取所有设备 - 完全基于缓存"""
    try:
        db = SessionLocal()
        try:
            devices = db.query(Device).all()
            device_dicts = [device.to_dict() for device in devices]

            # 确保每个设备字典包含 'dev_id'
            for device_dict in device_dicts:
                if "dev_id" not in device_dict:
                    device_dict["dev_id"] = device_dict.get("dev_id", "")

            # 更新缓存
            await device_cache.batch_update(device_dicts)
            logger.info(f"从数据库获取 {len(device_dicts)} 台设备并更新缓存")
            return device_dicts
        finally:
            db.close()
    except Exception as e:
        logger.error(f"获取设备列表失败: {e}")
        return []


async def get_device(dev_id: str):
    """获取单个设备 - 完全基于缓存"""
    if not dev_id:
        logger.warning("获取设备时dev_id为空")
        return None
    try:
        # 尝试从Redis获取
        cached_device = await device_cache.get_device(dev_id)
        if cached_device:
            logger.debug(f"从缓存获取设备 {dev_id} 成功")
            # 转换时间戳为东八区时间格式
            if "last_active" in cached_device:
                last_active = cached_device["last_active"]

                # 处理不同类型的时间数据
                if isinstance(last_active, (int, float)):
                    # 已经是时间戳数值
                    timestamp = last_active
                elif isinstance(last_active, str):
                    # 尝试解析格式化时间字符串
                    try:
                        # 尝试解析为时间戳
                        timestamp = float(last_active)
                    except ValueError:
                        # 尝试解析为格式化时间字符串
                        try:
                            dt_obj = datetime.datetime.strptime(last_active, "%Y-%m-%d %H:%M:%S")
                            timestamp = dt_obj.timestamp()
                        except Exception:
                            logger.error(f"无法解析时间格式: {last_active}")
                            timestamp = time.time()  # 使用当前时间作为默认值
                else:
                    logger.error(f"未知的时间格式类型: {type(last_active)}")
                    timestamp = time.time()

                # 转换为东八区时间
                utc_time = datetime.datetime.utcfromtimestamp(timestamp)
                beijing_tz = pytz.timezone('Asia/Shanghai')
                beijing_time = utc_time.replace(tzinfo=pytz.utc).astimezone(beijing_tz)
                # 格式化为字符串
                formatted_time = beijing_time.strftime("%Y-%m-%d %H:%M:%S")
                # 创建新字典避免修改原始缓存数据
                device_data = cached_device.copy()
                device_data["last_active"] = formatted_time
                return device_data
            return cached_device

        # 缓存未命中，从数据库获取
        db = SessionLocal()
        try:
            device = db.query(Device).filter(Device.dev_id == dev_id).first()
            if device:
                device_dict = device.to_dict()
                # 确保包含 'dev_id'
                device_dict["dev_id"] = dev_id

                # 处理 last_active 字段
                if "last_active" in device_dict:
                    last_active = device_dict["last_active"]

                    # 处理不同类型的时间数据
                    if isinstance(last_active, (int, float)):
                        # 已经是时间戳数值
                        timestamp = last_active
                    elif isinstance(last_active, str):
                        # 尝试解析格式化时间字符串
                        try:
                            # 尝试解析为时间戳
                            timestamp = float(last_active)
                        except ValueError:
                            # 尝试解析为格式化时间字符串
                            try:
                                dt_obj = datetime.datetime.strptime(last_active,
                                                                    "%Y-%m-%d %H:%M:%S")
                                timestamp = dt_obj.timestamp()
                            except Exception:
                                logger.error(f"无法解析时间格式: {last_active}")
                                timestamp = time.time()  # 使用当前时间作为默认值
                    else:
                        logger.error(f"未知的时间格式类型: {type(last_active)}")
                        timestamp = time.time()

                    device_dict["last_active"] = timestamp

                # 更新缓存（保持原始时间戳格式）
                await device_cache.update_device(dev_id, device_dict)

                # 转换时间戳为东八区时间格式
                if "last_active" in device_dict:
                    timestamp = device_dict["last_active"]
                    # 转换为东八区时间
                    utc_time = datetime.datetime.utcfromtimestamp(timestamp)
                    beijing_tz = pytz.timezone('Asia/Shanghai')
                    beijing_time = utc_time.replace(tzinfo=pytz.utc).astimezone(beijing_tz)
                    # 格式化为字符串
                    formatted_time = beijing_time.strftime("%Y-%m-%d %H:%M:%S")
                    device_dict["last_active"] = formatted_time

                return device_dict
            return None
        finally:
            db.close()
    except Exception as e:
        logger.error(f"获取设备失败: {e}")
        return None


async def update_device_status(dev_id: str, update_data: dict):
    """更新设备状态 - 不依赖外部数据库会话"""
    try:
        # 使用数据库上下文
        async with get_db_context() as db:
            device = db.query(Device).filter(Device.dev_id == dev_id).first()
            if device:
                # 确保数值字段有效
                for key in ["cpu_usage", "mem_usage", "brightness", "volume"]:
                    if key in update_data and update_data[key] == '':
                        update_data[key] = None

                # 更新设备状态
                for key, value in update_data.items():
                    setattr(device, key, value)

                # 提交更改
                db.commit()

                # 获取更新后的设备数据
                updated_device = device.to_dict()

                # 更新缓存
                await device_cache.update_device(dev_id, updated_device)

                # 推送更新给所有订阅的客户端
                await device_info_manager.broadcast_device_info(dev_id, {
                    "type": "device_update",
                    "data": updated_device
                })

                logger.info(f"设备状态更新成功: {dev_id}")
                return updated_device
            return None
    except Exception as e:
        logger.error(f"更新设备状态失败: {e}")
        return None
